package com.sztz.utils;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.sztz.entity.WxStatistics;
import com.sztz.entity.WxStatisticsForWeek;
import com.sztz.entity.WxUser;
import lombok.extern.slf4j.Slf4j;

import java.io.File;
import java.time.LocalDate;
import java.time.temporal.WeekFields;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.Optional;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * 小程序个人用户申报成功单据统计数据处理
 * @author yzy
 * @date 2021/11/25 18:27
 */
@Slf4j
public class WxStatisticsUtils {
    /**
     * Excel文件基础路径
     */
    private static final String BASE_EXCEL_PATH="D:\\20220301\\";
    /**
     * 读取企业EXCEL文件，获取微信小程序注册用户
     * @return insert语名
     * @author yzy
     * @date 2021/11/25 18:57
     */
    public static List<WxUser> readWxUserExcel(){
        return EasyExcel.read(new File(BASE_EXCEL_PATH+"wx_user.xls")).head(WxUser.class).sheet().doReadSync();
    }
    /**
     * 读取企业EXCEL文件，获取小程序大陆确报统计数据
     * @return insert语名
     * @author yzy
     * @date 2021/11/25 18:57
     */
    public static List<WxStatistics> readWxBundleExcel(){
        return EasyExcel.read(new File(BASE_EXCEL_PATH+"wx_bundle.xls")).head(WxStatistics.class).sheet().doReadSync();
    }
    /**
     * 读取企业EXCEL文件，获取小程序香港捆绑统计数据
     * @return insert语名
     * @author yzy
     * @date 2021/11/25 18:57
     */
    public static List<WxStatistics> readWxHkBundleExcel(){
        return EasyExcel.read(new File(BASE_EXCEL_PATH+"wx_hkbundle.xls")).head(WxStatistics.class).sheet().doReadSync();
    }
    /**
     * 读取企业EXCEL文件，获取小程序香港无缝统计数据
     * @return insert语名
     * @author yzy
     * @date 2021/11/25 18:57
     */
    public static List<WxStatistics> readWxHkRocarsExcel(){
        return EasyExcel.read(new File(BASE_EXCEL_PATH+"wx_hkrocars.xls")).head(WxStatistics.class).sheet().doReadSync();
    }
    /**
     * 组装小程序统计的SQL语句
     * @param list 统计数据
     * @param wxUser 小程序个人信息
     * @return insert语名
     * @author yzy
     * @date 2021/11/25 18:57
     */
    public static String createInsertSql(List<WxStatistics> list,WxUser wxUser,int type,String year,String month){
        String sql = "INSERT INTO TZ_STATISTICS.wx_statistics(login_code,total,create_month,type,send_year,send_month) VALUES('{}',{},'{}',{},'{}','{}');";
        Optional<WxStatistics> first = list.stream().filter(wxStatistics ->
            StrUtil.equalsAnyIgnoreCase(wxStatistics.getCreaterName(),wxUser.getLoginCode())
                    && StrUtil.equals(wxStatistics.getCreateYear(),year)
                    && StrUtil.equals(wxStatistics.getCreateMonth().split(StrUtil.DASHED)[1],month)
        ).findFirst();
        if(first.isPresent()){
            WxStatistics wxStatistics =first.get();
            return StrUtil.format(sql,wxUser.getLoginCode(),wxStatistics.getNum(),wxStatistics.getCreateMonth(),type,year, NumberUtil.parseInt(month));
        }
        return StrUtil.EMPTY;
    }

    /**
     * 读取统计数据EXCEL文件，输出SQL文件
     * @param wxUserList 企业信息
     * @author yzy
     * @date 2021/11/25 18:57
     */
    public static void doOutSql(List<WxUser> wxUserList,int type){
        String outFilePath= StrUtil.concat(Boolean.TRUE,BASE_EXCEL_PATH,type==0?"outsql\\wx_bundle_statistics.sql":type==1?"outsql\\wx_hk_bundle_statistics.sql":"outsql\\wx_hk_rocars_statistics.sql");
        List<WxStatistics> list = type==0?readWxBundleExcel():type==1?readWxHkBundleExcel():readWxHkRocarsExcel();
        log.info("{} 统计数据={}",type,list.size());
        List<String> sqlList = new ArrayList<>();
        wxUserList.forEach(item->
            //Stream.of("2022").forEach(year->
                Stream.of("01","02","03","04","05","06","07","08","09","10","11","12").forEach(month-> {
                            String sql = createInsertSql(list, item, type, "2022", month);
                            if(StrUtil.isNotBlank(sql)) {
                                sqlList.add(sql);
                            }
                        }
                )
            //)
        );
        log.info("{} sql数据={}",type,sqlList.size());
        FileUtil.appendUtf8Lines(sqlList,new File(outFilePath));
    }

    public static void main(String[] args) {
        //createWxUserStatus();
        doOutSql(readWxUserExcel(),2);
    }

    /**
     * 小程序用户状态
     * @author yzy
     * @date 2021/11/25 18:57
     */
    public static void createWxUserStatus(){
        String outFilePath= StrUtil.concat(Boolean.TRUE,BASE_EXCEL_PATH,"outsql\\wx_user.sql");
        //所有小程序个人账号
        List<WxUser> wxUsers = readWxUserExcel();
        //按月统计数据
        List<WxStatistics> bunldeList = readWxBundleExcel();
        List<WxStatistics> hkBundleList = readWxHkBundleExcel();
        List<WxStatistics> hkRocarsList = readWxHkRocarsExcel();
        bunldeList.addAll(hkBundleList);
        bunldeList.addAll(hkRocarsList);
        //最近三个月按周统计
        List<WxStatisticsForWeek> allWxWeek = getAllWxWeek();
        //生成SQL语名
        List<String> collect = wxUsers.stream().map(wxUser -> {
            List<WxStatisticsForWeek> userWeekList = allWxWeek.stream().filter(wxWeek -> StrUtil.equalsAnyIgnoreCase(wxWeek.getCreaterName(), wxUser.getLoginCode())).collect(Collectors.toList());
            //默认是从来没有申报过单据的用户
            int status = 3;
            //集合不为空说明最近三个月有做过单
            if(CollUtil.isNotEmpty(userWeekList)){
                status = 2;
                //三个月内有12个周都有做单就是超级活跃用户
                if(userWeekList.size()>=12){
                    log.info("超级活跃用户:{}",wxUser.getLoginCode());
                    status=0;
                }else {
                    //一个月内是否有申报记录
                    Optional<WxStatisticsForWeek> any = userWeekList.stream().filter(wxWeek -> wxWeek.getOwnWeek() >= getNowWeek() - 5).findAny();
                    if (any.isPresent()) {
                        status = 1;
                    }
                }
            }
            if(status==3){
                //有申报成功过
                Optional<WxStatistics> any = bunldeList.stream().filter(item -> StrUtil.equalsAnyIgnoreCase(item.getCreaterName(), wxUser.getLoginCode())).findAny();
                if(any.isPresent()){
                    status = 4;
                }
            }

            String sql = "INSERT INTO TZ_STATISTICS.wx_user(user_id,login_code,register_time,status) VALUES('{}','{}','{}',{});";
            return StrUtil.format(sql,wxUser.getId(),wxUser.getLoginCode(),wxUser.getRegisterTime(),status);
        }).collect(Collectors.toList());
        FileUtil.appendUtf8Lines(collect,new File(outFilePath));
    }

    /**
     * 读取企业EXCEL文件，获取小程序大陆确报周统计数据
     * @return insert语名
     * @author yzy
     * @date 2021/11/25 18:57
     */
    public static List<WxStatisticsForWeek> readWxBundleWeekExcel(){
        return EasyExcel.read(new File(BASE_EXCEL_PATH+"bundleforweek.xlsx")).head(WxStatisticsForWeek.class).sheet().doReadSync();
    }
    /**
     * 读取企业EXCEL文件，获取小程序香港捆绑周统计数据
     * @return insert语名
     * @author yzy
     * @date 2021/11/25 18:57
     */
    public static List<WxStatisticsForWeek> readWxHkBundleWeekExcel(){
        return EasyExcel.read(new File(BASE_EXCEL_PATH+"hkbundleforweek.xls")).head(WxStatisticsForWeek.class).sheet().doReadSync();
    }
    /**
     * 读取企业EXCEL文件，获取小程序香港无缝周统计数据
     * @return insert语名
     * @author yzy
     * @date 2021/11/25 18:57
     */
    public static List<WxStatisticsForWeek> readWxHkRocarsWeekExcel(){
        return EasyExcel.read(new File(BASE_EXCEL_PATH+"hkrocarsforweek.xls")).head(WxStatisticsForWeek.class).sheet().doReadSync();
    }

    private static List<WxStatisticsForWeek> getAllWxWeek(){
        int nowWeek = getNowWeek();
        //按周统计数据(只要最近三个月的)
        /*List<WxStatisticsForWeek> bundleWeekList = readWxBundleWeekExcel().stream().filter(item->item.getCreateYear()==2021 && item.getOwnWeek()>nowWeek-14).collect(Collectors.toList());
        List<WxStatisticsForWeek> hkBundleWeekList = readWxHkBundleWeekExcel().stream().filter(item->item.getCreateYear()==2021 && item.getOwnWeek()>nowWeek-14).collect(Collectors.toList());
        List<WxStatisticsForWeek> hkRocarsWeekList = readWxHkRocarsWeekExcel().stream().filter(item->item.getCreateYear()==2021 && item.getOwnWeek()>nowWeek-14).collect(Collectors.toList());*/
        List<WxStatisticsForWeek> bundleWeekList = readWxBundleWeekExcel();
        List<WxStatisticsForWeek> hkBundleWeekList = readWxHkBundleWeekExcel();
        List<WxStatisticsForWeek> hkRocarsWeekList = readWxHkRocarsWeekExcel();
        bundleWeekList.addAll(hkBundleWeekList);
        bundleWeekList.addAll(hkRocarsWeekList);
        return bundleWeekList.stream().distinct().collect(Collectors.toList());
    }
    /**
     * 今天是今年的第几周
     * @return int 今年第几周
     * @author yzy
     * @date 2021/11/26 14:57
     */
    private static int getNowWeek(){
        WeekFields weekFields = WeekFields.of(Locale.getDefault());
        return LocalDate.now().get(weekFields.weekOfWeekBasedYear());
    }

}
